<?php

namespace Muction\Tools\Builder\Constructor;

use Muction\Tools\Exception\BuilderException;
use Muction\Tools\Builder\Constructor\Lib\AbstractDatabase;

/**
 * MySQL 语句构建工具
 *
 * Class MySql
 * @package Muction\Tools\Builder\Constructor
 */
class MySql extends AbstractDatabase {

    static private $instance;

    private $firstSql='';

    private $lastSql='';

	/**
	 * 创建表所需配置项
	 * @var array
	 */
	protected $tableOption = [
		'name' => '', 'engine' => '', 'charset' => '', 'collate' => ''
	];

	/**
	 * mysql常用存储引擎
	 * @var array
	 */
	protected $engine =[
		'INNODB' ,'MYISAM' ,'MEMROY' ,'CSV' ,'FEDERATED' ,'ARCHIVE' ,'BLACKHOLE',
	];


	/**
	 * 字段设置项
	 * @var array
	 */
	protected $option = [
		'name' => '', 'length' => '', 'type' => '', 'comment' => '',
		'charset' => '', 'collate' => '', 'notnull' => '', 'null' => '',
		'default' => '', 'increment' => '',//(只有int类型支持自增)
		'index' => '', 'primary' => '' ,
		'unsigned'=>'',//无符号，支持整形浮点型
	];

	/**
	 * mysql支持的常用数据类型
	 * @var array
	 */
	protected $dataType = [

		// 可以设置子父级的数据类型
		'CHAR' ,//固定长度非二进制字符串 1~255个字符 用空格右侧填充补全
		'VARCHAR' ,//边长非二进制字符串 0~65535个字符 L+1字节 L<=M 和 1<=M<=255
		'TINYTEXT',//非常小的非二进制字符串 L+1 L<2的8次方
		'TEXT',//小的非二进制字符串 L+2 L<+2的16次方
		'MEDIUMTEXT',//中等大小的非二进制字符串 L+3 L<=2的24次方
		'LONGTEXT',//非二进制字符串 太大了不知道怎么表示了

		'TINYINT' , //1个字节  -128~127 0~255
		'SMALLINT' ,//2个字节 -32768~32767 0~65535
		'MEDIUMINT' , //3个字节 -8388608~8388607 0~16777215
		'INT' ,//4个字节 integer -2147483648~2147463647 0~4294967295 40亿
		'BIGINT',//8个字节 -922337203685477588~9223372036854775807 0~18446744073709551615
		'FLOAT' , //4个字节单精度
		'DOUBLE' ,//8个字节双精度
		'DECIMAL',//DECIMAL为定点小数 (M,N) m表示精度，n表示标度 ，定点数以字符串形式存储 。货币，科学数据推荐此数据类型
		'YEAR' ,//1个字节 1901-2155
		'TIME',//3个字节
		'DATE' ,//3个字节
		'DATETIME' ,//8个字节
		'TIMESTAMP' ,//4个字节

		'BINARY' ,
		'VARBINARY' ,
		'BLOB' ,
		'ENUM' ,//枚举类型，只能有一个枚举字符串值 1或2个字节 取决于枚举值的数目最大 65535 。//值内部用整数表示每个枚举值均有一个索引值从1开始编号
		'SET' ,//一个设置，字符串对象可以有零个或多个SET成员 1,2,3,4,8 最多64个成员 值内部用整数表示

		'BIT',//位字段类型 1~64默认为1
		'BINARY',//固定长度二进制字符串 类似于CHAR
		'VARBINARY',//可变长度二进制字符串 类似于VARCHAR

		'TINYBLOB',//非常小的BLOB
		'BLOB',//小BLOB 存储是二进制字符串（字节字符串） 没有字符集
		'MEDIUMBLOB',//中等大小BLOB
		'LONGBLOB',//非常大的BLOB
	];

	private $allowSetCharsetType= [];

	/**
	 * 字段转换成SQL暂存
	 * @var string
	 */
	protected $columnToSql="";

	public function __construct()
	{
		$this->allowSetCharsetType =array_slice($this->dataType, 0 ,6);
	}

	public static function getInstance(){

	    if(!self::$instance instanceof self){
	        self::$instance = new self();
        }

        return self::$instance;

    }


	/**
	 * 创建数据库
	 * @param string $databaseName
	 */
	public function createDatabase($databaseName=''){

		$this->database = $databaseName;
		$this->sql = 'CREATE DATABASE `'.$databaseName.'`';

	}


	/**
	 * 删除数据库
	 * @param string $databaseName
	 */
	public function dropDatabase($databaseName=''){

		$this->database = $databaseName;
		$this->sql = 'DROP DATABASE `'.$databaseName.'`';

	}


	public function alterTable($tableName="")
	{
		$this->table=$tableName;
		return MySqlAlterTable::getInstance($this);
	}

	/**
	 * 创建表
	 * @param string $tableName
	 */
	public function createTable($tableName = 'testTable')
	{
		$this->firstSql = 'CREATE TABLE ';

		if(is_array($tableName)){
			$tableNameOption=array_intersect_key($tableName,$this->tableOption);
			if(isset($tableNameOption['name'])){
				$this->firstSql .= "`{$tableNameOption['name']}`";
			}

			if(isset($tableNameOption['engine'])){
				if(!in_array($tableNameOption['engine'] , $this->engine)){
					throw BuilderException::invalidArgument("存储引擎{$tableNameOption['engine']}不合法");
				}
				$this->lastSql .= "ENGINE {$tableNameOption['engine']} \r\n";
			}

			if(isset($tableNameOption['charset'])){
				$this->lastSql .= "DEFAULT CHARACTER SET={$tableNameOption['charset']} \r\n";
			}

			if(isset($tableNameOption['collate'])){
				$this->lastSql .= "COLLATE={$tableNameOption['collate']} \r\n";
			}

		}else{
			$this->firstSql  .= "`{$tableName}` ";
		}


		return $this;


	}

	/**
	 * 对输入的字段配置进行过滤
	 * @param array $options
	 * @return $this
	 */
	public function column(array $options)
	{
		foreach ($options as $option){

			$filter=$this->filterOptions($option);
			array_push($this->column , $filter);
		}

		$this->columnToSql();

		$this->sql = $this->firstSql . $this->columnToSql . $this->lastSql;


	}

	private function filterOptions(array $option){
		return array_intersect_key($option,$this->option);
	}

	/**
	 * 将字段转换成SQL
	 */
	private function columnToSql(){

		$sql=[];
		$index=[];
		$primary=[];

		foreach($this->column as $columnValue){
			$item='';
			$columnType='';

			if(!isset($columnValue['name'])){
				throw BuilderException::invalidArgument("必须指定字段名称，配置项为name");
			}
			$item .= "`{$columnValue['name']}`";

			if(isset($columnValue['type'])){
				//字段类型
				$columnType = strtoupper($columnValue['type']);
				if(!in_array($columnType,$this->dataType)){
					throw BuilderException::invalidArgument('type 指定的字段('.$columnValue['name'].')类型('.$columnValue['type'].')不存在');
				}

				if($columnType == 'VARCHAR' && !isset($columnValue['length'])){
					throw BuilderException::invalidArgument('type 指定的字段('.$columnValue['name'].') VARCHAR 类型必须要指定长度');
				}

				$item .= " {$columnValue['type']}";

				if(isset($columnValue['length'])){
					$item .= "({$columnValue['length']})";
				}
			}

			if(in_array($columnType,$this->allowSetCharsetType)){

				if(isset($columnValue['charset'])){
					$item .= " CHARACTER SET {$columnValue['charset']}";
				}

				if(isset($columnValue['collate'])){
					$item .= " COLLATE {$columnValue['collate']}";
				}
			}

			//unsigned
			$unsignedItems=['INT','FLOAT'];
			if(isset($columnValue['unsigned']) && in_array( $columnType , $unsignedItems )){
				$item .= " UNSIGNED ";
			}


			//not null or null
			if(isset($columnValue['notnull'])){
				$item .= " NOT NULL";
			}else if(isset($columnValue['null'])){
				$item .= " NULL";
			}

			if(isset($columnValue['default']) && $columnType != 'INT') {
				$item .= " DEFAULT '{$columnValue['default']}' ";
			}

			if(isset($columnValue['comment'])){
				$item .= " COMMENT '{$columnValue['comment']}'";
			}

			if(isset($columnValue['index'])){
				array_push($index,$columnValue['name']);
			}

			if(isset($columnValue['primary'])){
				array_push($primary,$columnValue['name']);
			}

			//increment
			if( strtoupper($columnValue['type']) == 'INT' && isset($columnValue['increment'])){
				$item .= " AUTO_INCREMENT ";
			}

			//CHECKSUM=0
			//DELAY_KEY_WRITE=0

			array_push($sql,$item);
		}

		$sql= implode(',',$sql);
        $indexPrimary=[];
        $indexPrimarySql='';
        if(!empty($index)){
            $indexPrimary[]='INDEX (`'. implode('`,`',$index).'`)';
        }
        if(!empty($primary)){
            $indexPrimary[]='PRIMARY KEY (`'.implode('`,`',$primary).'`)';
        }

        if(!empty($indexPrimary)){
            $indexPrimarySql= ','.implode(',',$indexPrimary);
        }

		$this->columnToSql = '('.$sql . $indexPrimarySql.') ';

	}

}